package com.dao.impl;

import com.bean.*;
import com.dao.PurchaseDao;
import com.util.JDBC_Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class PurchaseDaoImpl implements PurchaseDao {

    Connection conn = null;
    ResultSet rs= null;
    PreparedStatement ps = null;

    @Override
    public Purchase purchaseInit(User user,Supplier supplier) {
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String nowDate = simpleDateFormat.format(date) + "";
        int userId = user.getId();
        int supplierId = supplier.getId();
        int purchaseId = 0;
        Purchase purchase = new Purchase();

        try {
        if(haveNotInPurchaseExist(user,supplier)){
            conn = JDBC_Util.getConnection();
            String sql1 = "select id from t_purchase where is_in_warehouse = 0 and user_id = ? and supplier_id = ?";
            ps = conn.prepareStatement(sql1);
            ps.setInt(1,userId);
            ps.setInt(2,supplierId);
            rs = ps.executeQuery();
            if (rs.next()) {
                purchaseId = rs.getInt("id");
            }

        }else {
            String sql = "insert into t_purchase values (null,"+userId+",'"+nowDate+"',"+supplierId+",0)";
            conn = JDBC_Util.getConnection();
            ps = conn.prepareStatement(sql);
            ps.executeUpdate();


            String sql1 = "select max(id) from t_purchase where is_in_warehouse = 0 and user_id = ? and supplier_id = ?";
            ps = conn.prepareStatement(sql1);
            ps.setInt(1,userId);
            ps.setInt(2,supplierId);
            rs = ps.executeQuery();
            if (rs.next()) {
                purchaseId = rs.getInt("max(id)");
            }
        }

        purchase.setUser(user);
        purchase.setPurchaseDate(nowDate);
        purchase.setSupplier(supplier);
        purchase.setId(purchaseId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return purchase;
    }

    @Override
    public List<PurchaseInfo> getAllGoods(Purchase purchase) {
        Supplier supplier = purchase.getSupplier();
        int supId = supplier.getId();
        int pid = purchase.getId();
        List<PurchaseInfo> purchaseInfos = new ArrayList<>();

        conn = JDBC_Util.getConnection();

        String sql = "select t_purchase_info.id,goods_id,goods_name,manufactor,buy_price,sell_price,number from t_purchase_info" +
                " inner join t_goods tg on t_purchase_info.goods_id = tg.id" +
                " inner join t_purchase tp on t_purchase_info.purchase_Id = tp.id" +
                " inner join t_supplier ts on tp.supplier_id = ts.id" +
                " where ts.id = ? and tp.id = ?";

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,supId);
            ps.setInt(2,pid);
            rs = ps.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("id");
                int goodsId = rs.getInt("goods_id");
                String goodsName = rs.getString("goods_name");
                String manufactor = rs.getString("manufactor");
                int buyPrice = rs.getInt("buy_price");
                int sellPrice = rs.getInt("sell_price");
                int number = rs.getInt("number");

                Goods goods = new Goods();
                goods.setId(goodsId);
                goods.setName(goodsName);
                goods.setManufactor(manufactor);
                goods.setBuyPrice(buyPrice);
                goods.setSellPrice(sellPrice);
                PurchaseInfo pu= new PurchaseInfo();
                pu.setGoods(goods);
                pu.setPurchase(purchase);
                pu.setNumber(number);
                pu.setId(id);

                purchaseInfos.add(pu);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return purchaseInfos;
    }

    @Override
    public int addGoods(Goods goods, int number, Purchase purchase) {
        int buyPrice = goods.getBuyPrice();
        int sellPrice = goods.getSellPrice();
        String type = goods.getType();
        String image = goods.getImage();
        String name = goods.getName();
        String manufactor = goods.getManufactor();

        int id = 0;
        int m = 0;
        int n = 0;
        try {
             if(isGoodsExist(goods)){
                 conn = JDBC_Util.getConnection();
                 String sql1 = "select id from t_goods where goods_name = ?";
                 ps = conn.prepareStatement(sql1);
                 ps.setString(1,name);
                 rs = ps.executeQuery();
                 if (rs.next()) {
                     id = rs.getInt("id");
                 }
            }else {
                 conn = JDBC_Util.getConnection();
                 String sql2 = "insert into t_goods values (null,?,?,?,?,?,?)";
                 ps = conn.prepareStatement(sql2);
                 ps.setString(1,name);
                 ps.setString(2,manufactor);
                 ps.setString(3,type);
                 ps.setInt(4,buyPrice);
                 ps.setInt(5,sellPrice);
                 ps.setString(6,null);
                 m  = ps.executeUpdate();

                 String sql5 = "select max(id) from t_goods";
                 id = JDBC_Util.returnMaxId(sql5);

             }

             int pId = purchase.getId();

            String sql3 ="insert into t_purchase_info values (null,"+id+","+number+","+pId+")";
            n = JDBC_Util.update(sql3);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return n;
    }

    @Override
    public int deleteGoods(int key) {
        String sql = "delete from t_purchase_info where id = "+key+"";
        int m = JDBC_Util.update(sql);
        return m;
    }

    @Override
    public int updateGoodsNumber(int key, int number) {
        String sql = "update t_purchase_info set number = "+number+" where id = "+key+"";
        int m = JDBC_Util.update(sql);
        return m;
    }

    @Override
    public int inWarehouse(List<PurchaseInfo> purchaseInfos) {
        conn = JDBC_Util.getConnection();
        int purchaseId = 0;
        int m = 0;

        try {
            for (PurchaseInfo purchaseInfo:purchaseInfos) {
                purchaseId = purchaseInfo.getPurchase().getId();
                int number = purchaseInfo.getNumber();
                Goods goods = purchaseInfo.getGoods();
                int goodsId = goods.getId();
                String sql = "select count(0) from t_stock where goods_id = ?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1,goodsId);
                rs = ps.executeQuery();
                int i = 0;
                if (rs.next()) {
                    i = rs.getInt("count(0)");
                }
                if(i > 0){
                    String sql1 = "update t_stock set number = number + "+number+" where goods_id = "+goodsId+"";
                    m += JDBC_Util.update(sql1);
                }else {
                    String sql2 = "insert into t_stock values (null,"+goodsId+","+number+")";
                    m += JDBC_Util.update(sql2);
                }
            }

            String sql3 = "update t_purchase set is_in_warehouse = 1  where id =  "+purchaseId+" ";
            m = JDBC_Util.update(sql3);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return m;
    }

    @Override
    public boolean haveNotInPurchaseExist(User user,Supplier supplier) {
        int supplierId = supplier.getId();
        int id = user.getId();
        String sql = "select count(0) from t_purchase where user_id = ?" +
                " and is_in_warehouse = 0 and supplier_id = ?";
        conn = JDBC_Util.getConnection();
        boolean flag = false;
        int i = 0;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.setInt(2,supplierId);
            rs = ps.executeQuery();
            if (rs.next()) {
                i = rs.getInt("count(0)");
            }
            if (i > 0) {
                flag = true;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return flag;
    }

    public boolean isGoodsExist(Goods goods) {
        String name = goods.getName();
        String sql = "select count(0) from t_goods where goods_name = ?";
        conn = JDBC_Util.getConnection();
        boolean flag = false;
        int i = 0;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();
            if (rs.next()) {
                i = rs.getInt("count(0)");
            }
            if (i > 0) {
                flag = true;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return flag;
    }

    @Override
    public List<Purchase> getAllPurchase() {
        List<Purchase> purchases = new ArrayList<>();
        conn = JDBC_Util.getConnection();
        String sql = "select t_purchase.id, tu.real_name, purchase_date, ts.supplier_name from t_purchase" +
                " inner join t_users tu on t_purchase.user_id = tu.id" +
                " inner join t_supplier ts on t_purchase.supplier_id = ts.id" +
                " where is_in_warehouse = 1";

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String realName = rs.getString("real_name");
                String purchaseDate = rs.getString("purchase_date");
                String supplierName = rs.getString("supplier_name");

                Supplier supplier = new Supplier();
                supplier.setName(supplierName);
                User user = new User();
                user.setRealName(realName);
                Purchase purchase = new Purchase();
                purchase.setUser(user);
                purchase.setSupplier(supplier);
                purchase.setPurchaseDate(purchaseDate);
                purchase.setId(id);

                purchases.add(purchase);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return purchases;
    }

    public Purchase getSelectPurchase(int key){
        Purchase purchase = new Purchase();
        conn = JDBC_Util.getConnection();

        String sql = "select ts.id as ts_id,purchase_date,tu.real_name,ts.supplier_name from t_purchase" +
                " inner join t_users tu on t_purchase.user_id = tu.id" +
                " inner join t_supplier ts on t_purchase.supplier_id = ts.id " +
                " where t_purchase.id = "+key+"";


        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                int supplierId =rs.getInt("ts_id");
                String purchaseDate = rs.getString("purchase_date");
                String realName = rs.getString("real_name");
                String supplierName = rs.getString("supplier_name");

                Supplier supplier = new Supplier();
                supplier.setName(supplierName);
                supplier.setId(supplierId);
                User user = new User();
                user.setRealName(realName);
                purchase.setId(key);
                purchase.setSupplier(supplier);
                purchase.setUser(user);
                purchase.setPurchaseDate(purchaseDate);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return purchase;
    }

    @Override
    public List<PurchaseInfo> getPurchaseInfo(Purchase purchase) {
        Supplier supplier = purchase.getSupplier();
        int supId = supplier.getId();
        int pid = purchase.getId();
        List<PurchaseInfo> purchaseInfos = new ArrayList<>();

        conn = JDBC_Util.getConnection();

        String sql = "select t_purchase_info.id,goods_id,goods_name,manufactor,buy_price,sell_price,number from t_purchase_info" +
                " inner join t_goods tg on t_purchase_info.goods_id = tg.id" +
                " inner join t_purchase tp on t_purchase_info.purchase_Id = tp.id" +
                " inner join t_supplier ts on tp.supplier_id = ts.id" +
                " where ts.id = ? and tp.id = ?";

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,supId);
            ps.setInt(2,pid);
            rs = ps.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("id");
                int goodsId = rs.getInt("goods_id");
                String goodsName = rs.getString("goods_name");
                String manufactor = rs.getString("manufactor");
                int buyPrice = rs.getInt("buy_price");
                int sellPrice = rs.getInt("sell_price");
                int number = rs.getInt("number");

                Goods goods = new Goods();
                goods.setId(goodsId);
                goods.setName(goodsName);
                goods.setManufactor(manufactor);
                goods.setBuyPrice(buyPrice);
                goods.setSellPrice(sellPrice);
                PurchaseInfo pu= new PurchaseInfo();
                pu.setGoods(goods);
                pu.setPurchase(purchase);
                pu.setNumber(number);
                pu.setId(id);

                purchaseInfos.add(pu);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return purchaseInfos;
    }

    public List<Purchase> getFindPurchase(String supplierName,String startDate,String endDate){
        List<Purchase> list = new ArrayList<>();
        conn = JDBC_Util.getConnection();

        String sql = "select t_purchase.id, tu.real_name, purchase_date, ts.supplier_name from t_purchase" +
                " inner join t_users tu on t_purchase.user_id = tu.id" +
                " inner join t_supplier ts on t_purchase.supplier_id = ts.id" +
                " where is_in_warehouse = 1 ";

        if(!supplierName.equals("")  && supplierName != null){
            sql += " and supplier_name like '%"+supplierName+"%'";
        }

        if(!startDate.equals("")  && startDate != null){
            sql += " and purchase_date > '"+startDate+"'";
        }

        if(!endDate.equals("")  && endDate != null){
            sql += " and purchase_date < '"+endDate+"'";
        }

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String realName = rs.getString("real_name");
                String purchaseDate = rs.getString("purchase_date");
                String newSupplierName = rs.getString("supplier_name");

                Supplier supplier = new Supplier();
                supplier.setName(newSupplierName);
                User user = new User();
                user.setRealName(realName);
                Purchase purchase = new Purchase();
                purchase.setUser(user);
                purchase.setSupplier(supplier);
                purchase.setPurchaseDate(purchaseDate);
                purchase.setId(id);

                list.add(purchase);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return list;
    };
}
